<html>
    <head>
        <meta http-equiv=Content-Type content="text/html; charset=UTF-8">
        <script src="../js/jquery.js" type="text/javascript"></script>
        <script src='../js/thickbox-compressed.js' type='text/javascript'></script>
        <link rel='stylesheet' href='../css/thickbox.css' type='text/css' media='screen' />
        <script type="text/javascript">
            function llamaModal(rq){
                window.open ('historialRq.php?rq=' + rq, 'Historial_Incidencias', "width=900,height=600,scrollbars=YES");
            }
        </script>
    <style type="text/css">
        .estadistica{
            border-collapse: separate;
            border-spacing: 10px 2px;
            border-style: outset;
            border-width: 5px;
        }
        .estadistica caption{
            font-weight: bolder;
            font-size: 16px;
            font-style: italic;
        }
        .estadistica th{
            font-style: oblique;
            font-weight: bolder;
            background-color:#58ACFA;
            color: white;
            font-size: 14px;
        }
        .estadistica tfoot{
                    font-weight: bolder;
                    background-color:#848484;
                    color: white;
        }
        .estadistica td{
            border-style: groove;
            border-width: 2px;
            text-align: center;
        }
    </style>
    </head>
<body>
<?php
    //recibe los parametros de la busqueda
    $sistema = $_REQUEST['sistema'];
    $reqId  = $_REQUEST['reqId'];
    $empresa = $_REQUEST['empresa'];
    $responsable = $_REQUEST['responsable'];
    $estado = $_REQUEST['estado'];
    $tipo   = $_REQUEST['tipo'];
    $estResp = $_REQUEST['estResp'];
    $fecIni = $_REQUEST['fecIni'];
    $fecFin = $_REQUEST['fecFin'];

    //Valido los valores, si vienen vacios se manipulan para hacer la consulta
    if ($fecIni == "")
        $fecIni = "1800-01-01";
    if ($fecFin == "")
        $fecFin = "2888-12-31";
    $fecFin .= " 23:59:59";

    $where = " where req.reqFec between '$fecIni' and '$fecFin' ";
    $joinPri = "left join solucionesRq sol on sol.reqId = req.reqId and sol.solRqId in (select max(reqAux.solRqId) from solucionesRq reqAux where reqAux.reqId = req.reqId) ";
    if($responsable != "" || $estado != "" || $tipo  != "" || $estResp !=""){
        $join =  " ";
        $join .= ($responsable != "")? "and sol.solRqUsr = '$responsable' " : " ";
        $join .= ($estado != "")? "and sol.estRqId = $estado " : " ";
        $join .= ($tipo != "")? "and sol.tipRqId = $tipo " : " ";
        if($estResp != "")
            $join .= ($estResp == "pendientes" )? "and sol.estRqId <> 3 " : "and sol.estRqId = 3 ";
    }

    $where .= ($reqId != "")? "and req.reqId = $reqId " : " ";
    $where .= ($empresa != "")? "and emp.empresa = '$empresa' " : " ";
    $where .= ($sistema != "")? "and sis.sisId = '$sistema' " : " ";
    //Se construye la consulta maestra
        $sql = "select distinct (req.reqId) as Inc, req.reqFec as Fecha
                    ,concat(usr.nombres,' ',usr.apellidos) as Usuario
                    ,emp.texto4 as Empresa,req.reqDes as Descripcion
                    ,sis.sisNombre as Sistema
                    ,concat('<a href=\"../requerimientos/',req.reqArc,'\">',req.reqArc,'</a>') as Adjunto
                from requerimientos req
                $joinPri 
                inner join usuarios usr on usr.username = req.reqUsr
                inner join empresas emp on emp.empresa = usr.entrada
                inner join sistemas sis on sis.sisId = req.sisId " . $where .$join;
// change these to match your mySQL host's settings
mysql_connect("localhost", "caweb", "caweb");
mysql_select_db("intranetpccom");

// require the class
require "../librerias/grilla/class.datagrid.php";
//echo "antes-$sql";
// instantiate the class and pass the query string to it
$grid = new dataGrid($sql);

// show all the columns
$grid->showColumn("Inc");
$grid->showColumn("Fecha");
$grid->showColumn("Usuario");
$grid->showColumn("Empresa");
$grid->showColumn("Descripcion");
$grid->showColumn("Sistema");

// sort the data by the "title" column
$grid->setDefaultSortColumn("Inc");

// create a function that will set a javascript action for when clicking on the rows
// you could use this to redirect the page
function action($value_of_clicked_field, $array_with_the_values_of_all_fields_in_clicked_row)
{
    return "llamaModal('".$array_with_the_values_of_all_fields_in_clicked_row["Inc"]."')";
    //return "javascript:llamaModal('".$array_with_the_values_of_all_fields_in_clicked_row["Inc"]."')";
}

// bound the function to the rows
$grid->setRowActionFunction("action");

// we add a custom column
$grid->showCustomColumn("Archivo");

// we add content to it by using a callback function
// first create the function
function custom_content($value,$array_with_the_values_of_all_fields_in_clicked_row)
{
    
    return $array_with_the_values_of_all_fields_in_clicked_row['Adjunto'];
    //return "<a href=\"javascript:if(confirm('Are you sure?{$array_with_the_values_of_all_fields_in_clicked_row['Inc']}')){}else{}\">delete</a>]";
}
// bound this function to the "operations" column's fields
$grid->setCallbackFunction("Archivo", "custom_content");
// disable sorting on it
$grid->disableSorting("Archivo");
// unset for this column the action function set before
$grid->unsetActionFunction("Archivo");

// add a title to the table
$grid->setHeaderHTML("<h4 style='color:red'>Click sobre una Incidencia para ver el historial</h4>");

// and also add a footer
// you could use this button to dynamically change the form's action and submit the checked values...
$grid->setFooterHTML("<br /><input type='hidden' value='$sistema' name='sistema'>
<input type='hidden' value='$reqId' name='reqId'>
<input type='hidden' value='$empresa' name='empresa'>
<input type='hidden' value='$responsable' name='responsable'>
<input type='hidden' value='$estado' name='estado'>
<input type='hidden' value='$tipo' name='tipo'>
<input type='hidden' value='$estResp' name='estResp'>
<input type='hidden' value='$fecIni' name='fecIni'>
<input type='hidden' value='$fecFin' name='fecFin'>");
// witness magic!
$grid->render();
        $sql = "select req.* from requerimientos req            
            group by req.reqId";
        $result = mysql_query($sql) or die ("Error SQL 1".mysql_error().$sql);
        $totalSol = mysql_num_rows($result);
        $sql = "select sol.tipRqId,tip.tipRqNombre, count(*) as porTipo from requerimientos req
                $joinPri 
                inner join usuarios usr on usr.username = req.reqUsr
                inner join empresas emp on emp.empresa = usr.entrada
                inner join tiposRq tip on tip.tipRqId = sol.tipRqId
                inner join sistemas sis on sis.sisId = req.sisId   $where $join group by sol.tipRqId";
//        echo $sql;
        $tipos = mysql_query($sql) or die ("Error SQL ".mysql_error().$sql);
        echo "<table class='estadistica'>";
        echo "<caption>Estadistica por Tipificacion Incidencias Asignadas</caption>";
        echo "<tr>";
        echo "<th>Tipo</th>";
        echo "<th>Cantidad</th>";
        echo "<th>%</th>";
        echo "</tr>";
        $tmpTot = 0;
        while ($row1 = mysql_fetch_array($tipos)) {
            echo "<tr>";
            echo "<td>".$row1['tipRqNombre']."</td>";
            echo "<td>".$row1['porTipo']."</td>";
            echo "<td>".(round(($row1['porTipo'] / $totalSol),4) * 100)."</td>";
            echo "</tr>";
            $tmpTot = $tmpTot + $row1['porTipo'];
        }
        echo "<tfoot><tr><td>TOTAL INCIDENCIA</td><td colspan='2'>".$tmpTot."</td></tr></tfoot>";
        echo "</table><br/>";
        //consulta estadisticas por Personal de Sistemas
        $sql = "select usr.username, sol.estRqId, count(req.reqId) as porUsr, usr.nombres,usr.apellidos from requerimientos req
                $joinPri 
                inner join usuarios usr on usr.username = sol.solRqUsr
                inner join usuarios usre on usre.username = req.reqUsr
                inner join empresas emp on emp.empresa = usre.entrada
                inner join sistemas sis on sis.sisId = req.sisId " .
                $where."$join group by usr.username,sol.estRqId order by usr.username ";
//        echo $sql;
        $usuario = mysql_query($sql) or die ("Error SQL ".mysql_error().$sql);
        echo "<td>";    //columna tabla principal
        echo "<table class='estadistica'>";
        echo "<caption>Estadistica Incidencias Asignadas por Personal</caption>";
        echo "<tr>";
        echo "<th>Nombre</th>";
        echo "<th>Inc Pendientes</th>";
        echo "<th>Inc Realizadas</th>";
        echo "<th>Efectividad(%)</th>";
        echo "<th>Total</th>";
        echo "</tr>";
        $usuariControl = "";
        $penXusr = 0;
        $resXusr = 0;
        $gtot = 0;
        $gpenXusr = 0;
        $gresXusr = 0;
        $nombre = "";
        while ($row1 = mysql_fetch_array($usuario)) {
            if($usuariControl == "" || $usuariControl == $row1['username']){
                $nombre = $row1['nombres']." ".$row1['apellidos'];
                $usuariControl = $row1['username'];
                if($row1['estRqId'] == 3)
                    $resXusr = $resXusr + $row1['porUsr'];
                else
                    $penXusr = $penXusr + $row1['porUsr'];
            }
            else{
                $tot = $resXusr + $penXusr;
                $gtot = $gtot + $tot;
                $gpenXusr = $gpenXusr + $penXusr;
                $gresXusr = $gresXusr + $resXusr;
                echo "<tr>";
                echo "<td>".$nombre."</td>";
                echo "<td>".$penXusr."</td>";
                echo "<td>".$resXusr."</td>";
                echo "<td>".(round(($resXusr / $tot),2) * 100)."</td>";
                echo "<td>".$tot."</td>";
                echo "</tr>";
                $nombre = $row1['nombres']." ".$row1['apellidos'];
                $usuariControl = $row1['username'];
                $penXusr = 0;
                $resXusr = 0;
                if($row1['estRqId'] == 3)
                    $resXusr = $resXusr + $row1['porUsr'];
                else
                    $penXusr = $penXusr + $row1['porUsr'];
            }
        }
        if($usuariControl != ""){
            $tot = $resXusr + $penXusr;
            $gtot = $gtot + $tot;
            $gpenXusr = $gpenXusr + $penXusr;
            $gresXusr = $gresXusr + $resXusr;
            echo "<tr>";
            echo "<td>".$nombre."</td>";
            echo "<td>".$penXusr."</td>";
            echo "<td>".$resXusr."</td>";
            echo "<td>".(round(($resXusr / $tot),4) * 100)."</td>";
            echo "<td>".$tot."</td>";
            echo "</tr>";
            echo "<tfoot>";
            echo "<tr>";
            echo "<td>TOTAL INCIDENCIAS</td>";
            echo "<td>".$gpenXusr."</td>";
            echo "<td>".$gresXusr."</td>";
            echo "<td colspan=2>".$gtot."</td>";
            echo "</tr>";
            echo "</tfoot>";
        }
        echo "</table><br/>";
        $where1 = " where req.reqFec between '$fecIni' and '$fecFin' ";
        $where1 .= ($reqId != "")? "and req.reqId = $reqId " : " ";
        $where1 .= ($empresa != "")? "and emp.empresa = '$empresa' " : " ";
        //consulta estadistica por sistema
        $sql = "select sis.sisId, sis.sisNombre,count(req.reqId) as porSis from requerimientos req
                inner join usuarios usr on usr.username = req.reqUsr
                inner join empresas emp on emp.empresa = usr.entrada
                inner join sistemas sis on sis.sisId = req.sisId " .
                $where1."group by sis.sisId order by sis.sisId";
        $sistemas = mysql_query($sql) or die ("Error SQL ".mysql_error().$sql);
        echo "<table class='estadistica'>";
        echo "<caption>Estadistica por Sistema</caption>";
        echo "<tr>";
        echo "<th>Sistema</th>";
        echo "<th>Incidencias</th>";
        echo "</tr>";
        $tmpTot = 0;
        while ($row1 = mysql_fetch_array($sistemas)) {
            echo "<tr>";
            echo "<td>".$row1['sisNombre']."</td>";
            echo "<td>".$row1['porSis']."</td>";
            echo "</tr>";
            $tmpTot = $tmpTot + $row1['porSis'];
        }
        echo "<tfoot><tr><td>TOTAL</td><td>".$tmpTot."</td></tr></tfoot>";
        echo "</table><br/>";
        //consulta estadistica por empresa
        $sql = "select emp.empresa, emp.texto4,count(req.reqId) as porEmp from requerimientos req
                inner join usuarios usr on usr.username = req.reqUsr
                inner join sistemas sis on sis.sisId = req.sisId 
                inner join empresas emp on emp.empresa = usr.entrada " .
                $where."group by emp.empresa order by emp.texto4";
        $empresas = mysql_query($sql) or die ("Error SQL ".mysql_error().$sql);
        echo "<table class='estadistica'>";
        echo "<caption>Estadistica por Empresa</caption>";
        echo "<tr>";
        echo "<th>Empresa</th>";
        echo "<th>Incidencias</th>";
        echo "</tr>";
        $tmpTot = 0;
        while ($row1 = mysql_fetch_array($empresas)) {
            echo "<tr>";
            echo "<td>".$row1['texto4']."</td>";
            echo "<td>".$row1['porEmp']."</td>";
            echo "</tr>";
            $tmpTot = $tmpTot + $row1['porEmp'];
        }
        echo "<tfoot><tr><td>TOTAL</td><td>".$tmpTot."</td></tr></tfoot>";
        echo "</table><br/>";
?>
</body>
</html>
